﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using CoreLab.Oracle; //using Devart.Data.Oracle; //using Oracle.DataAccess.Client;
using System.Web;
using System.Web.Caching;


using LMD.Constant;
using LMD.Lib.Oracle;

namespace Cached
{
    /// <summary>
    /// Summary description for CompanyReport_OpportunityTable
    /// </summary>
    public class CompanyReport_OpportunityTable : CacheAbstraction
    {
        public DataTable GetOpportunityRekap(int iCompanyId, DateTime dtmStart, DateTime dtmEnd)
        {
            return GetOpportunityRekap(iCompanyId, dtmStart, dtmEnd, false);
        }
        public DataTable GetOpportunityRekap(int iCompanyId, DateTime dtmStart, DateTime dtmEnd, bool bForceRefresh)
        {
            String sxCacheKey = String.Format("{0}{1}_{2}_{3}",
                KCache.V_REPORT_OPPORTUNITY_REKAP, 
                iCompanyId.ToString(),
                dtmStart.ToShortDateString(),
                dtmEnd.ToShortDateString());

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib(Connection.WEB_USER);

                List<OracleParameter> lsParams = new List<OracleParameter>();

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmID";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iCompanyId;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmStart";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
                lsParams[lsParams.Count - 1].Value = dtmStart;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmEnd";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
                lsParams[lsParams.Count - 1].Value = dtmEnd;

                String sxSelect =
                    "SUM(DECODE(P_OPPORTUNITY_STATUS_ID, 1, 1, 0)) as \"CLOSED - CANCEL\", " +
                    "SUM(DECODE(P_OPPORTUNITY_STATUS_ID, 2, 1, 0)) as \"CLOSED - LOST\", "+
                    "SUM(DECODE(P_OPPORTUNITY_STATUS_ID, 3, 1, 0)) as \"CLOSED - WON\", "+
                    "SUM(DECODE(P_OPPORTUNITY_STATUS_ID, 4, 1, 0)) as \"INACTIVE\", "+
                    "SUM(DECODE(P_OPPORTUNITY_STATUS_ID, 5, 1, 0)) as \"OPEN\", "+
                    "VALUE, "+
                    "MONTH, "+
                    "YEAR";
                String sxQuery = String.Format(
                    @"
                    select
                        {0}
                    from
                        (
                            select
                                b.P_OPPORTUNITY_STATUS_ID,
                                TO_CHAR(a.CREATED_DATE, 'MON/YY') VALUE,
                                CAST(TO_CHAR(a.CREATED_DATE, 'MM') as number) MONTH,
                                CAST(TO_CHAR(a.CREATED_DATE, 'YYYY') as number) YEAR
                            from
                                slogix.T_SLX_OPPORTUNITY a
                                left join slogix.P_OPPORTUNITY_STATUS b
                                    on a.P_OPPORTUNITY_STATUS_ID = b.P_OPPORTUNITY_STATUS_ID
                            where
                                a.COMPANY_ID = :prmID
                                and TRUNC(a.CREATED_DATE) >= TRUNC(:prmStart)
                                and TRUNC(a.CREATED_DATE) < TRUNC(:prmEnd + 1)
                        )
                    group by
                        VALUE,
                        MONTH,
                        YEAR
                    order by
                        YEAR,
                        MONTH
                    ",sxSelect);

                DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());

                AddToCache(sxCacheKey, dt, CacheType.Daily);
                
            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }

        public DataTable GetOpportunityDetail(int iCompanyId, DateTime dtmStart, DateTime dtmEnd)
        {
            return GetOpportunityDetail(iCompanyId, dtmStart, dtmEnd, false);
        }
        public DataTable GetOpportunityDetail(int iCompanyId, DateTime dtmStart, DateTime dtmEnd, bool bForceRefresh)
        {
            String sxCacheKey = String.Format("{0}{1}_{2}_{3}",
                KCache.V_REPORT_OPPORTUNITY_DETAIL,
                iCompanyId.ToString(),
                dtmStart.ToShortDateString(),
                dtmEnd.ToShortDateString());

            if(bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib(Connection.WEB_USER);

                List<OracleParameter> lsParams = new List<OracleParameter>();

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmID";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iCompanyId;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmStart";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
                lsParams[lsParams.Count - 1].Value = dtmStart;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmEnd";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
                lsParams[lsParams.Count - 1].Value = dtmEnd;

                String sxQuery =
                    @"
                    select
                        a.OPPORTUNITY_NO,
                        b.STATUS,
                        a.CREATED_DATE START_DATE,
                        c.END_DATE,
                        d.STAGE_NAME
                    from
                        slogix.T_SLX_OPPORTUNITY a
                        left join slogix.P_OPPORTUNITY_STATUS b
                            on a.P_OPPORTUNITY_STATUS_ID = b.P_OPPORTUNITY_STATUS_ID
                        left join slogix.T_OPPORTUNITY_STAGE c
                            on a.CURRENT_STAGE_ID = c.T_OPPORTUNITY_STAGE_ID
                        left join slogix.P_OPPORTUNITY_STAGE d
                            on c.P_OPPORTUNITY_STAGE_ID = d.P_OPPORTUNITY_STAGE_ID
                    where
                         a.COMPANY_ID = :prmID
                        and TRUNC(a.CREATED_DATE) >= TRUNC(:prmStart)
                        and TRUNC(a.CREATED_DATE) < TRUNC(:prmEnd + 1)
                    ";

                DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());

                AddToCache(sxCacheKey, dt, CacheType.Daily);
                
            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }        

        public override String GetCacheFilename()
        {
            return KCache.Filename.COMPANY_REPORT_VIEWS;
        }
    }
}